package Homework;

import java.sql.*;
import java.util.Scanner;

/**
 * @author shkstart
 * @creat 2022-05-14:19
 */
public class Dbutil {
    public static final String link = "com.mysql.jdbc.Driver";
    public static final String url = "jdbc:mysql://localhost:3306/class?useSSL=false&characterEncoding=UTF-8&serverTimezone=GMT%2b8";
    public static final String userName = "root";
    public static final String pwd = "rose";
    public static Scanner sc = new Scanner(System.in);

    /**
     * 获得链接
     *
     * @return
     */
    public static boolean getDriver(String str) {
        try {
            Class.forName(str);
            return true;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 获得Connection的对象
     *
     * @param url
     * @param userName
     * @param pwd
     * @return DriverManager.getConnection(url, userName, pwd)
     */
    public static Connection getCon(String url, String userName, String pwd) {
        try {
            return DriverManager.getConnection(url, userName, pwd);
        } catch (SQLException e) {
            e.printStackTrace();

        }
        return null;

    }

    /**
     * 返回一个Statement接口的实现类的对象
     *
     * @param con
     * @return
     */
    public static Statement getStatement(Connection con) {
        try {
            return con.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();

        }
        return null;
    }

    /**
     * 执行查询操作
     *
     * @param sql
     * @return 需要返回结果集
     */
    public static ResultSet getResultSet(String sql) {
        try {
            return st.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();

        }
        return null;
    }

    public static int updOrDelOrins(String sql) {
        try {
            return st.executeUpdate(sql);
        } catch (SQLException e) {
            //e.printStackTrace();
            return 0;
        }

    }

    /**
     * 关闭资源
     *
     * @param con Connection接口
     * @param st  Statement接口
     */
    public static void closeRescource(Connection con, Statement st) {

        try {
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static int addSubject() {
        System.out.println("输入课程名称");
        String subName = sc.next();
        System.out.println("输入课程时间");
        int classHour = sc.nextInt();
        System.out.println("输入年级id");
        int gradeId = sc.nextInt();
        try {
            PreparedStatement ps = con.prepareStatement("insert into s_subject (subjectname,classhour,gradeid) values (?,?,?)");
            ps.setString(1, subName);
            ps.setInt(2, classHour);
            ps.setInt(3, gradeId);
            return ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }
    /**
     * 根据课程编号修改课程信息
     * @param subId
     */
    private static void updateSubMsg(int subId) {
        a:
        while (true) {
            System.out.println("修改信息：");
            System.out.println("1.编号 2.名称 3.时长 4.年级编号 5.退出");
            System.out.println("请选择：");
            int no = sc.nextInt();
            switch (no) {
                case 1: {
                    System.out.println("输入新编号");
                    int newId = sc.nextInt();
                    String sql = "update s_subject  set id=" + newId + "where id =" + subId + ";";
                    int i = updOrDelOrins(sql);
                    System.out.println(i > 0 ? "修改成功" : "修改失败");
                    break;
                }
                case 2: {
                    System.out.println("输入新名称");
                    String newName = sc.next();
                    String sql = "update s_subject  set subjectname=" + "\'" + newName + "\'" + "where id =" + subId + ";";
                    int i = updOrDelOrins(sql);
                    System.out.println(i > 0 ? "修改成功" : "修改失败");
                    break;
                }
                case 3: {
                    System.out.println("输入新时长");
                    int newhour = sc.nextInt();
                    String sql = "update s_subject set classhour= '" + newhour + "'where id ='" + subId + "';";
                    int i = updOrDelOrins(sql);
                    System.out.println(i > 0 ? "修改成功" : "修改失败");
                    break;
                }
                case 4: {
                    System.out.println("输入新年级编号");
                    int newGradeid = sc.nextInt();
                    String sql = "update s_subject  set gradeid=" + newGradeid + "where id =" + subId + ";";
                    int i = updOrDelOrins(sql);
                    System.out.println(i > 0 ? "修改成功" : "修改失败");
                    break;
                }
                case 5: {
                    System.out.println("退出修改!");
                    break a;
                }
            }
        }
    }
    /**
     * 添加学生
     * @return
     */
    private static int addStu() {
        try {
            PreparedStatement ps = con.prepareStatement("insert into s_student(sname,sex,gradeid,fromcity,birthday)values (?,?,?,?,?)");
            System.out.println("输入姓名：");
            ps.setString(1,sc.next());
            System.out.println("输入性别：");
            ps.setString(2,sc.next());
            System.out.println("输入年级编号：");
            ps.setInt(3,sc.nextInt());
            System.out.println("输入城市：");
            ps.setString(4,sc.next());
            System.out.println("输入生日：");
            ps.setDate(5,Date.valueOf(sc.next()));
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    public static Connection con = null;
    public static Statement st = null;
    public static ResultSet rs = null;

    /**
     * 程序的入口
     *
     * @param args
     */
    public static void main(String[] args) {
        if (!getDriver(link)) {
            System.out.println("没有找到链接！");
        }
        con = getCon(url, userName, pwd);
        st = getStatement(con);
        exit:
        while (true) {
            System.out.println("1.查询 2.增加 3.删除 4.修改 5.退出");
            int choice = sc.nextInt();
            switch (choice) {
                case 1: {
                    System.out.println("统计学生总数:");
                    //sc.nextLine();
                    System.out.println("输入查询语句");
                    sc.nextLine();
                    String sql = sc.nextLine();
                    rs = getResultSet(sql);
                    try {
                        while (rs.next()) {
                            int sums = rs.getInt("sums");
                            System.out.println("学生总人数:" + sums);
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    break;
                }
                case 2: {
                    while (true) {
                        System.out.println("1.增加课程，2.增加学生 (输入其他数字退出)");
                        int no = sc.nextInt();
                        if (no == 1) {
                            int i = addSubject();
                            if (i > 0) {
                                System.out.println("增加成功！");
                            } else
                                System.out.println("增加失败！");
                        } else if (no == 2) {
                            int i = addStu();
                            System.out.println(i>0?"添加成功":"添加失败");
                        } else
                            break;
                    }
                    break;
                }
                case 3: {
                    break;
                }
                case 4: {
                    System.out.println("根据编号修改课程信息");
                    System.out.println("输入课程编号：");
                    int subId = sc.nextInt();
                    updateSubMsg(subId);
                    break;
                }
                case 5: {
                    closeRescource(con, st);
                    System.out.println("退出");
                    break exit;
                }
            }

            //select count(stuno) as sums from s_student;
//        insert into s_subject (subjectname,classhour,gradeid) values(造原子弹,2000,17);
        }
    }



}
